import pandas as pd
from literature.models.Literature import Literature
from literature.models.Institution import Institution
from literature.models.Author import Author
from literature.models.GlobalTables import Language, LiteratureStatusType, LiteratureType, JournalPublicationCycleType, InstitutionType
from literature.models.Publications import Journal
from control.models.User import User
from django.core.exceptions import ObjectDoesNotExist
from datetime import datetime
import re

language_map = {
    "AZER": "Azerbaijani",
    "BULG": "Bulgarian",
    "CHIN": "Chinese",
    "CZEC": "Czech",
    "DANH": "Danish",
    "DUTH": "Dutch",
    "ENGL": "English",
    "FREN": "French",
    "GERM": "German",
    "HUNG": "Hungarian",
    "ITAL": "Italian",
    "JAPN": "Japanese",
    "KORE": "Korean",
    "POLH": "Polish",
    "PORT": "Portuguese",
    "ROM":  "Romanian",
    "ROMN": "Romanian",
    "ROUM": "Romanian",
    "RUSS": "Russian",
    "SLOE": "Slovak",
    "SLWN": "Slovene", # 无对应
    "SPAN": "Spanish",
    "SRCR": "Serbian",
    "SWED": "Swedish",
    "UKRN": "Ukrainian",
    "ZZ":   "unknown",
    "ar": "Arabic",
    "cs": "Czech",
    "de": "German",
    "en": "English",
    "es": "Spanish",
    "fr": "French",
    "hu": "Hungarian",
    "it": "Italian",
    "ja": "Japanese",
    "nb": "Norwegian Bokmål",
    "nl": "Dutch",
    "no": "Norwegian",
    "pl": "Polish",
    "ru": "Russian",
    "sr": "Serbian",
    "tr": "Turkish"
}

def init_literature():
    try:
        df = pd.read_excel("./files/literature_data.xlsx", engine='openpyxl')
    except Exception as e:
        print(f"Error reading the Excel file: {e}")
        exit(1)
        
    user = User.objects.get(user_account="superadmin")
    pub_cycle = JournalPublicationCycleType.objects.filter(name='unknown')[0]
    status = LiteratureStatusType.objects.filter(name='Available')[0]
    liter_type = LiteratureType.objects.filter(name='Unspecified')[0]
    inst_type = InstitutionType.objects.filter(name='unknown')[0]
    
    # 遍历每一行
    for index, row in df.iterrows():
        # 将每一行转换为字典形式，便于后续操作
        if index > 10:
            break
        if index > 0:
            row_data = row.to_dict()
            
            title = None if pd.isna(row_data['title']) or row_data["title"] == "" else row_data['title']
            if title is not None:
                author_ids = []
                if not pd.isna(row_data['author']) and row_data['author'] != "":
                    if ';' in row_data['author'] and not row_data['author'].endswith(';'):
                        authors = row_data['author'].split(';')
                    else:
                        authors = re.split(r'\s*,\s*', row_data['author'])
                    for author_name in authors:
                        if len(author_name) <= 128:
                            author, created = Author.objects.get_or_create(name=author_name, creator=user, last_editor=user)
                            author_ids.append(author.uuid)
                        
                language = language_map.get(row_data['language'], "unknown")
                try:
                    language = Language.objects.get(name=language)
                except ObjectDoesNotExist:
                    # 如果没有找到，则使用 name 为 'unknown' 的 Language 实例
                    language = Language.objects.get(name="unknown")
                
                # 不懂的字段全扔备注
                comment = ""
                if not pd.isna(row_data['coden']) and row_data['coden'] != "":
                    comment += f"coden: {row_data['coden']}\n"
                # 无类型代码的对应关系
                if not pd.isna(row_data['type']) and row_data['type'] != "":
                    comment += f"type: {row_data['type']}\n"
                if not pd.isna(row_data['DocID']) and row_data['DocID'] != "":
                    comment += f"DocID: {row_data['DocID']}\n"
                if not pd.isna(row_data['RefID']) and row_data['RefID'] != "":
                    comment += f"RefID: {row_data['RefID']}\n"
                if not pd.isna(row_data['collection']) and row_data['collection'] != "":
                    comment += f"collection: {row_data['collection']}\n"
                if not pd.isna(row_data['corporate-source']) and row_data['corporate-source'] != "":
                    comment += f"corporate-source: {row_data['corporate-source']}\n"
                if not pd.isna(row_data['editor']) and row_data['editor'] != "":
                    comment += f"editor: {row_data['editor']}\n"
                if not pd.isna(row_data['monograph-title']) and row_data['monograph-title'] != "":
                    comment += f"monograph-title: {row_data['monograph-title']}\n"
                
                cit_name = "SEI"
                cit_id = None if pd.isna(row_data['PCDataID']) or row_data['PCDataID'] == "" else row_data['PCDataID']
                doi = None if pd.isna(row_data['DOI']) or row_data['DOI'] == "" else row_data['DOI']
                abstract = None if pd.isna(row_data['abstract']) or row_data['abstract'] == "" else row_data['abstract']
                issue = None if pd.isna(row_data['issue']) or row_data['issue'] == "" else row_data['issue']
                volume = None if pd.isna(row_data['volume']) or row_data['volume'] == "" else row_data['volume']
                
                year = None if pd.isna(row_data['issued']) or row_data['issued'] == "" else row_data['issued']
                if type(year) != int:
                    if type(year) == str and year.isdigit():
                        year = int(year)
                    else:
                        year = None
                date = None if year is None or year <= 0 else datetime(year, 1, 1).date()
                
                if row_data['container-title'] is None and row_data["ISSN"] is None:
                    pub_type = "unknown"
                    journal = None
                else:
                    pub_type = "Journal"
                    institutions = []
                    if not pd.isna(row_data['publisher']):
                        ins, _ = Institution.objects.get_or_create(name=row_data['publisher'], inst_type=inst_type, creator=user, last_editor=user)
                        institutions.append(ins.uuid)
                    journal, created = Journal.objects.get_or_create(name=row_data['container-title'], issn=row_data["ISSN"], \
                                                                    publication_cycle = pub_cycle, \
                                                                    creator=user, last_editor=user)
                    journal.institutions.set(institutions)
                    journal.save()
                    
                if pd.isna(row_data['page']) or row_data['page'] == "":
                    first_page = None
                    last_page = None
                else:
                    if "-" in row_data['page']:
                        pages = row_data['page'].split('-')
                        first_page, last_page = pages[0], pages[1]
                    else:
                        first_page = row_data['page']
                        last_page = row_data['page']
                    first_page = first_page if first_page.isdigit() else None
                    last_page = last_page if last_page.isdigit() else None
                    
                l = Literature.objects.create(title=title, zh_title=title, cit_name=cit_name,\
                                    cit_id=cit_id, abstract=abstract, DOI=doi, language=language,\
                                    comment=comment, pub_type=pub_type, pub_id=journal, issue=issue,\
                                    volume=volume, first_page=first_page, last_page=last_page, date=date,\
                                    creator=user, last_editor=user, status=status, liter_type=liter_type)
            
                l.authors.set(author_ids)
                l.save()
                
                print(f"Created Literature {index}: {l.title}")